The goal of this project is to pull together a database of the various NYC transportation options to ease and accelerate future analyses. Individual files to clean and analyze the data are in the folders: Citi-bike, Subway-turnstiles, Taxi:
Citi-bike.RSubway_turnstiles.RTaxi.RCreate_database.R: creates a database of the Citi-bike, Subway, and (eventually) the Taxi data. Shell scripts in each folder must be run first to download the data
Once the database is created, data can easily be accessed via SQL and dbplyr queries:
# establish the connection to the database
conn <- dbConnect(RSQLite::SQLite(), "NYC.db")
# query on disk
turnstile.df <- tbl(conn, "turnstile.2019.09")
turnstile.df %>%
select(Station, Time, Entries, Exits) %>%
group_by(Station) %>%
summarize(Entries = sum(Entries),
Exits = sum(Exits))
# or pulled into memory
turnstile.df <- tbl(conn, "turnstile.2019.09") %>% as_tibble()